﻿package dut.ir.lxb.weibo.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dut.ir.lxb.weibo.bean.UserActionBean;
import dut.ir.lxb.weibo.bean.UserWeiboAction;
import dut.ir.lxb.weibo.util.ActionType;

public class UserWeiboActionDao
{
	
	private UserWeiboActionDao()
	{
		
	}
	
	private static UserWeiboActionDao ud;
	
	public static UserWeiboActionDao getInstance()
	{
		if(ud == null)
		{
			ud = new UserWeiboActionDao();
			return ud;
		}
		else
			return ud;
				
	}
	
	public boolean hasRecord(String mid, String uid, ActionType actionType)
	{
		String tableName = "";
		String uidName = "";
		if(actionType.equals(ActionType.LIKE))
		{
			tableName = "T_Like";
			uidName = "likeuid";
		}
		else if(actionType.equals(ActionType.COMMENT))
		{
			tableName = "T_Comment";
			uidName = "commentuid";
		}
		else if(actionType.equals(ActionType.FORWARD))
		{
			tableName = "T_Forward";
			uidName = "forwarduid";
		}
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = String.format("select * from %s where mid=? and %s=?;", tableName, uidName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, mid);
			ps.setString(2, uid);
			
			ResultSet rs = ps.executeQuery();
			boolean flag = false;
			if(rs.next())
			{
				flag = true;
			}
			rs.close();
			ps.close();
			return flag;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
	}
	
	public void insertActionList(List<UserWeiboAction> actList)
	{
		if(actList == null || actList.size() == 0)
			return ;
		for(UserWeiboAction uwa : actList)
		{
			if(uwa.getActionType().equals(ActionType.LIKE))
				insertLike(uwa);
			else if(uwa.getActionType().equals(ActionType.COMMENT))
				insertComment(uwa);
			else if(uwa.getActionType().equals(ActionType.FORWARD))
				insertForward(uwa);
		}
	}
	
	public boolean insertLike(UserWeiboAction uwa)
	{
		
		if(uwa == null || !uwa.getActionType().equals(ActionType.LIKE))
		{
			System.err.println("对不起，您传入的参数有误，插入点赞数据库失败。");
			return false;
		}
		if(hasRecord(uwa.getMid(), uwa.getUid(), ActionType.LIKE))
			deleteUserWeiboAction(uwa.getMid(), uwa.getUid(), ActionType.LIKE);
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into T_Like (mid,likeuid,owneruid,ownernickname,likenickname) values(?,?,?,?,?);";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, uwa.getMid());
			ps.setString(2, uwa.getUid());
			ps.setString(3, uwa.getOwneruid());
			ps.setString(4, uwa.getOwnerNickname());
			ps.setString(5, uwa.getNickname());
			
			boolean flag = ps.execute();
			
			ps.close();
			return flag;
			
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
	}
	
	
	public boolean insertComment(UserWeiboAction uwa)
	{
		if(uwa == null || !uwa.getActionType().equals(ActionType.COMMENT))
		{
			System.err.println("对不起，您传入的参数有误，插入评论数据库失败。");
			return false;
		}
		if(hasRecord(uwa.getMid(), uwa.getUid(), ActionType.COMMENT))
			deleteUserWeiboAction(uwa.getMid(), uwa.getUid(), ActionType.COMMENT);
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into T_Comment (mid,commentuid,commentnickname,owneruid,ownernickname,content,time) values(?,?,?,?,?,?,?);";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, uwa.getMid());
			ps.setString(2, uwa.getUid());
			ps.setString(3, uwa.getNickname());
			ps.setString(4, uwa.getOwneruid());
			ps.setString(5, uwa.getOwnerNickname());
			ps.setString(6, uwa.getContent());
			ps.setString(7, uwa.getDatetime());
			
			boolean flag = ps.execute();
			ps.close();
			return flag;
			
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			//释放连接
			connectionMan.freeConnection(conn);
		}
	}
	
	
	public boolean deleteUserWeiboAction(String mid, String uid, ActionType actionType)
	{
		String tableName = "";
		String uidName = "";
		if(actionType.equals(ActionType.LIKE))
		{
			tableName = "T_Like";
			uidName = "likeuid";
		}
		else if(actionType.equals(ActionType.COMMENT))
		{
			tableName = "T_Comment";
			uidName = "commentuid";
		}
		else if(actionType.equals(ActionType.FORWARD))
		{
			tableName = "T_Forward";
			uidName = "forwarduid";
		}
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = String.format("delete from %s where mid=? and %s=?;", tableName, uidName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, mid);
			ps.setString(2, uid);
			
			boolean flag = ps.execute();
			ps.close();
			return flag;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
	}
	
	
	public boolean insertForward(UserWeiboAction uwa)
	{
		if(uwa == null || !uwa.getActionType().equals(ActionType.FORWARD))
		{
			System.err.println("对不起，您传入的参数有误，插入转发数据库失败。");
			return false;
		}
		if(hasRecord(uwa.getMid(), uwa.getUid(), ActionType.FORWARD))
			deleteUserWeiboAction(uwa.getMid(), uwa.getUid(), ActionType.FORWARD);
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into T_Forward (mid,forwarduid,forwardnickname,owneruid,ownernickname,time,content,path,forwardmid) values(?,?,?,?,?,?,?,?,?);";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, uwa.getMid());
			ps.setString(2, uwa.getUid());
			ps.setString(4, uwa.getOwneruid());
			ps.setString(3, uwa.getNickname());
			ps.setString(5, uwa.getOwnerNickname());
			ps.setString(7, uwa.getContent());
			ps.setString(6, uwa.getDatetime());
			ps.setString(8, uwa.getPath());
			ps.setString(9, uwa.getForwardmid());
			
			boolean flag = ps.execute();
			ps.close();
			return flag;
			
		}
		catch (SQLException e)
		{
			System.err.println(String.format("用户[%s]的微博[%s]转发信息插入数据库失败，内容为[%s], 错误信息为[%s]", uwa.getNickname(), uwa.getMid(), uwa.getContent(), e.getMessage()));
			// TODO Auto-generated catch block
//			e.printStackTrace();
			return false;
		} 
		catch(Exception e)
		{
			System.err.println(String.format("用户[%s]的微博[%s]转发信息插入数据库失败，内容为[%s], 错误信息为[%s]", uwa.getNickname(), uwa.getMid(), uwa.getContent(), e.getMessage()));
//			e.printStackTrace();
			return false;
		}
		finally
		{
			//释放连接
			connectionMan.freeConnection(conn);
		}
	}
	
	/**
	 * 通过微博ID获得行为数量
	 * @param mid 微博MID
	 * @param actionType 行为类型
	 * @return 行为数量
	 */
	public int getUserActionCountByMid(String mid, ActionType actionType)
	{
		if(actionType == ActionType.COMMENT) return getCommentCountByMid(mid);
		else if(actionType == ActionType.FORWARD) return getForwardCountByMid(mid);
		else return getLikeCountByMid(mid);
			
	}
	
	
	/**
	 * 获得一条微博的点赞数
	 * @param mid 微博MID
	 * @return 点赞数
	 */
	public int getLikeCountByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select count(*) from T_Like where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			int likeCount = 0;
			ResultSet rs = ps.executeQuery();
			if(rs.next())
			{
				likeCount = rs.getInt(1);
			}
			
			rs.close();
			ps.close();
			
			return likeCount;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return 0;
	}
	
	/**
	 * 获得一条微博的点赞列表
	 * @param mid 微博MID
	 * @return 点赞列表
	 */
	public List<UserWeiboAction> getLikeListByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select * from T_Like where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			
			List<UserWeiboAction> uwaList = new ArrayList<UserWeiboAction>();
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				UserWeiboAction uwa = new UserWeiboAction();
				
				uwa.setActionType(ActionType.LIKE);
				uwa.setMid(mid);
				uwa.setUid(rs.getString("likeuid"));
				uwa.setNickname(rs.getString("likenickname"));
				uwa.setOwneruid(rs.getString("owneruid"));
				uwa.setOwnerNickname(rs.getString("ownernickname"));
				
				uwaList.add(uwa);
			}
			
			rs.close();
			ps.close();
			
			return uwaList;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return null;
	}
	
	/**
	 * 通过MID获得微博评论数量
	 * @param mid 微博MID
	 * @return 评论数量
	 */
	public int getCommentCountByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select count(*) from T_Comment where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			
			ResultSet rs = ps.executeQuery();
			int commentCount = 0;
			while(rs.next())
			{
				commentCount = rs.getInt(1);
			}
			
			rs.close();
			ps.close();
			
			return commentCount;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return 0;
	}
	
	/**
	 * 通过MID获得微博评论列表
	 * @param mid 微博MID
	 * @return 评论列表
	 */
	public List<UserWeiboAction> getCommentListByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select * from T_Comment where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			
			List<UserWeiboAction> uwaList = new ArrayList<UserWeiboAction>();
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				UserWeiboAction uwa = new UserWeiboAction();
				
				uwa.setActionType(ActionType.COMMENT);
				uwa.setMid(mid);
				uwa.setUid(rs.getString("commentuid"));
				uwa.setNickname(rs.getString("commentnickname"));
				uwa.setOwneruid(rs.getString("owneruid"));
				uwa.setContent(rs.getString("content"));
				uwa.setDatetime(rs.getString("time"));
				uwa.setOwnerNickname(rs.getString("ownernickname"));
				
				uwaList.add(uwa);
			}
			
			rs.close();
			ps.close();
			
			return uwaList;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return null;
	}
	
	
	/**
	 * 通过微博ID获得转发数
	 * @param mid 微博ID
	 * @return 转发数
	 */
	public int getForwardCountByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select count(*) from T_Forward where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			
			ResultSet rs = ps.executeQuery();
			int forwardCount = 0;
			while(rs.next())
			{
				forwardCount = rs.getInt(1);
			}
			
			rs.close();
			ps.close();
			
			return forwardCount;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return 0;
	}
	
	/**
	 * 通过微博ID获得转发信息
	 * @param mid 微博ID
	 * @return 转发列表
	 */
	public List<UserWeiboAction> getForwardListByMid(String mid)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select * from T_Forward where mid=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, mid);
			
			List<UserWeiboAction> uwaList = new ArrayList<UserWeiboAction>();
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				UserWeiboAction uwa = new UserWeiboAction();
				
				uwa.setActionType(ActionType.FORWARD);
				uwa.setMid(mid);
				uwa.setUid(rs.getString("forwarduid"));
				uwa.setNickname(rs.getString("forwardnickname"));
				uwa.setOwneruid(rs.getString("owneruid"));
				uwa.setDatetime(rs.getString("time"));
				uwa.setContent(rs.getString("content"));
				uwa.setPath(rs.getString("path"));
				uwa.setForwardmid(rs.getString("forwardmid"));
				uwa.setOwnerNickname(rs.getString("ownernickname"));
				
				uwaList.add(uwa);
			}
			
			rs.close();
			ps.close();
			
			return uwaList;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return null;
	}
	
	/**
	 * 获得对用户交互行为最多的用户昵称及交互数量
	 * @param uid 
	 * @param actionType 行为类型，包括点赞、评论、转发
	 * @param k 前K个
	 * @return 交互用户行为列表
	 */
	public List<UserActionBean> getTopkActionUsers(String uid, ActionType actionType, int k)
	{
		//首先从数据库连接池中获得连接
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select %s,%s, count(*) as actioncount from %s where owneruid=? group by %s order by actioncount desc limit ?";
			
			
			String tableName, actionUid, actionNickname;
			if(actionType == ActionType.COMMENT)
			{
				tableName = "T_Comment";
				actionUid = "commentuid";
				actionNickname = "commentnickname";
			}
			else if(actionType == ActionType.LIKE)
			{
				tableName = "T_Like";
				actionUid = "likeuid";
				actionNickname = "likenickname";
			}
			else
			{
				tableName = "T_Forward";
				actionUid = "forwarduid";
				actionNickname = "forwardnickname";
			}
			
			sqlStr = String.format(sqlStr, actionUid, actionNickname, tableName, actionUid);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			//填充参数
			ps.setString(1, uid);
			ps.setInt(2, k);
			
			List<UserActionBean> uwaList = new ArrayList<UserActionBean>();
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				UserActionBean uwa = new UserActionBean();
				
				uwa.setOwnerUid(uid);
				uwa.setActionUid(rs.getString(1));
				uwa.setActionNickname(rs.getString(2));
				uwa.setActionCount(rs.getInt(3));
				
				uwaList.add(uwa);
			}
			
			rs.close();
			ps.close();
			
			return uwaList;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return null;						
		
	}

	
	
	
	
	
	
}
